﻿using KPIS.GERP.GBMS.APP_UI.Common;
using KPIS.GERP.GBMS.BLL;
using KPIS.GERP.GBMS.IBLL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GFMS.APP.App_UI.Commons;
using System;
using System.Collections.Generic;
using System.Data;
using Telerik.Web.UI;
using System.Linq;
using KPIS.GERP.GBMS.Reports;
using CrystalDecisions.Shared;
using KPIS.DBM;

namespace KPIS.GERP.GBMS.APP_UI.Form
{
    public partial class BGM_Project_Payment_Report : System.Web.UI.Page
    {
        private Main main = new Main();
        private IBGMProjectPaymentReportBLL PaymentReport = new BGMProjectPaymentReportBLL();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                txtBgmYear2.Text = SystemInfo._CurrentYearReq.ToString();
            }
        }

        #region report 2

        private DataTable GetData()
        {
            DateTime? dtStartDate = txtStartDate.GetDateValue();
            DateTime? dtEndDate = txtEndDate.GetDateValue();
            int? nBgmYear = main.ConvertNullInt(txtBgmYear2.Text);

            if (!dtStartDate.HasValue || !dtEndDate.HasValue || !nBgmYear.HasValue)
            {
                main.MessageAlert(Page, "กรอกข้อมูลให้ครบถ้วน");
                return null;
            }

            DBManager db = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            db.Open();
            db.CreateParameters(3);
            db.AddParameters(0, "@bgm_year", nBgmYear.Value);
            db.AddParameters(1, "@start_date", dtStartDate.Value);
            db.AddParameters(2, "@end_date", dtEndDate.Value);

            string strSQL = @"
select pj.bgm_proj_code as project_code
, pj.bgm_proj_name as project_name
, sub.bgm_strat_list_name as sub_act_name
, act.bgm_strat_list_name as act_name
, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) as amount11
, coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) as amount12
, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) + coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) as amount1
, coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0) as amount21
, coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0) as amount22
, coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0) + (coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0)) as amount2
, coalesce(mr.amount1, 0) - coalesce(p.amount1, 0) as amount31
, coalesce(mr.amount2, 0) - coalesce(p.amount2, 0) as amount32
, coalesce(mr.amount1, 0) - coalesce(p.amount1, 0) + (coalesce(mr.amount2, 0) - coalesce(p.amount2, 0)) as amount3
, coalesce(p.amount1, 0) - coalesce(r.amount1, 0) as amount41
, coalesce(p.amount2, 0) - coalesce(r.amount2, 0) as amount42
, coalesce(p.amount1, 0) - coalesce(r.amount1, 0) + (coalesce(p.amount2, 0) - coalesce(r.amount2, 0)) as amount4
, (coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0)) + (coalesce(mr.amount1, 0) - coalesce(p.amount1, 0)) + (coalesce(p.amount1, 0) - coalesce(r.amount1, 0)) as use_amount1
, (coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0)) + (coalesce(mr.amount2, 0) - coalesce(p.amount2, 0)) + (coalesce(p.amount2, 0) - coalesce(r.amount2, 0)) as use_amount2
, ((coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0)) + (coalesce(mr.amount1, 0) - coalesce(p.amount1, 0)) + (coalesce(p.amount1, 0) - coalesce(r.amount1, 0)))
+ (coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0)) + (coalesce(mr.amount2, 0) - coalesce(p.amount2, 0)) + (coalesce(p.amount2, 0) - coalesce(r.amount2, 0)) as use_amount

, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) - (coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0)) - (coalesce(mr.amount1, 0) - coalesce(p.amount1, 0)) - (coalesce(p.amount1, 0) - coalesce(r.amount1, 0)) as remain_amount1
, coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) - (coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0)) - (coalesce(mr.amount2, 0) - coalesce(p.amount2, 0)) - (coalesce(p.amount2, 0) - coalesce(r.amount2, 0)) as remain_amount2
, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) - (coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0)) - (coalesce(mr.amount1, 0) - coalesce(p.amount1, 0)) - (coalesce(p.amount1, 0) - coalesce(r.amount1, 0))
+ coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) - (coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0)) - (coalesce(mr.amount2, 0) - coalesce(p.amount2, 0)) - (coalesce(p.amount2, 0) - coalesce(r.amount2, 0)) as remain_amount
, '' as print_date

from bgm_project pj
left join bgm_strategy_data_mapping map1 on pj.bgm_strat_list_seq = map1.bgm_strat_list_seq
	and map1.bgm_year = pj.bgm_year
left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq
left join bgm_strategy_data_list sub on map1.bgm_strat_list_seq = sub.bgm_strat_list_seq
left join bgm_strategy_data_list act on map2.bgm_strat_list_seq = act.bgm_strat_list_seq

left join (select pje.bgm_proj_seq
	, sum(case when pje.bgm_source_seq = 1 then coalesce(pje.total_amount, 0) else 0 end) as amount1
	, sum(case when pje.bgm_source_seq = 11 then coalesce(pje.total_amount, 0) else 0 end) as amount2
	from bgm_project_expense pje
	group by pje.bgm_proj_seq
) pje on pj.bgm_proj_seq = pje.bgm_proj_seq

left join (select pje.bgm_proj_seq
	, sum(case when pje.bgm_source_seq = 1 then coalesce(rpt.cal_amount, 0) else 0 end) as amount1
	, sum(case when pje.bgm_source_seq = 11 then coalesce(rpt.cal_amount, 0) else 0 end) as amount2
	from bgm_project_report rpt
	left join bgm_project_expense pje on rpt.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where rpt.record_status <> 'D'
	and rpt.rpt_type in (2,3)
	and rpt.created_when >= @start_date
	and rpt.created_when <= @end_date
	group by pje.bgm_proj_seq
) rpt1 on pj.bgm_proj_seq = rpt1.bgm_proj_seq

left join (select pje.bgm_proj_seq
	, sum(case when pje.bgm_source_seq = 1 then (COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty else 0 end) as amount1
	, sum(case when pje.bgm_source_seq = 11 then (COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty else 0 end) as amount2

	from IcmPurchaseRequestItem pri
  inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId and po.RecordStatus = 'A'
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	inner join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where po.RecordStatus = 'A'
	and po.Closed = 0
	and pri.RecordStatus <> 'D'
    and po.BgYear = @bgm_year
	and po.OrderDate >= @start_date
	and po.OrderDate <= @end_date
	group by pje.bgm_proj_seq
) po on pj.bgm_proj_seq = po.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when mr.bgm_source_seq = 1 then coalesce(mrd.amount, 0) else 0 end) as amount1
	, sum(case when mr.bgm_source_seq = 11 then coalesce(mrd.amount, 0) else 0 end) as amount2
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	inner join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
	inner join IcmPurchaseOrder po on dn.PoId = po.PoId
	
	where mr.record_status = 'A'
	and mr.money_req_date >= @start_date
	and mr.money_req_date <= @end_date
	and mr.event_seq = 11
	and po.Closed = 0
	group by op.bgm_proj_seq
) mrpo on pj.bgm_proj_seq = mrpo.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when mr.bgm_source_seq = 1 then coalesce(mrd.amount, 0) else 0 end) as amount1
	, sum(case when mr.bgm_source_seq = 11 then coalesce(mrd.amount, 0) else 0 end) as amount2
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where mr.record_status = 'A'
	and mr.money_req_date >= @start_date
	and mr.money_req_date <= @end_date
	group by op.bgm_proj_seq
) mr on pj.bgm_proj_seq = mr.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when p.bgm_source_seq = 1 then coalesce(p.amount, 0) else 0 end) as amount1
	, sum(case when p.bgm_source_seq = 11 then coalesce(p.amount, 0) else 0 end) as amount2
	from fim_payments p
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where p.record_status = 'A'
	and p.pay_date >= @start_date
	and p.pay_date <= @end_date
	group by op.bgm_proj_seq
) p on pj.bgm_proj_seq = p.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when r.bgm_source_seq = 1 then coalesce(r.amount, 0) else 0 end) as amount1
	, sum(case when r.bgm_source_seq = 11 then coalesce(r.amount, 0) else 0 end) as amount2
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where r.record_status not in ('D', 'C')
	and r.receive_date >= @start_date
	and r.receive_date <= @end_date
	group by op.bgm_proj_seq
) r on pj.bgm_proj_seq = r.bgm_proj_seq

where pj.record_status = 'A'
and pj.bgm_year = @bgm_year
and pj.prj_type_seq not in (5, 6)

order by project_code"
;
            DataTable dtb = db.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            db.Dispose();

            foreach (DataRow dr in dtb.Rows)
            {
                dr["print_date"] = dtStartDate.Value.ToLongDateString() + " ถึง " + dtEndDate.Value.ToLongDateString();
            }

            return dtb;
        }

        protected void btnPrintReport2_Click(object sender, EventArgs e)
        {
            var dtb = GetData();
            ProjectReport rpt = new ProjectReport();

            rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
            rpt.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "ProjectReport");
        }

        protected void btnPrintReport3_Click(object sender, EventArgs e)
        {
            var dtb = GetData();
            ProjectReport rpt = new ProjectReport();

            rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
            rpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "ProjectReport");
        }

        private class Record
        {
            public Record()
            {
                MonthPayment = new Dictionary<int, KeyValuePair<decimal, decimal>>();
            }

            public int UniqueId { get; set; }

            public int StSeq { get; set; }
            public string StName { get; set; }

            public int SbStSeq { get; set; }
            public string SbStName { get; set; }

            public int BgmProjSeq { get; set; }
            public string BgmProjCode { get; set; }
            public string BgmProjName { get; set; }

            public decimal AvailBudgetAmount { get; set; }
            public decimal AvailOutBudgetAmount { get; set; }

            public int RowIndex { get; set; }
            private Dictionary<int, KeyValuePair<decimal, decimal>> MonthPayment { get; set; }

            public void AddOrUpdateMonthPayment(int monthCreate, decimal paymentBudgetValue, decimal paymentOutBudgetValue)
            {
                var newValue = new KeyValuePair<decimal, decimal>(paymentBudgetValue, paymentOutBudgetValue);
                if (MonthPayment.All(o => o.Key != monthCreate))
                {
                    MonthPayment.Add(monthCreate, newValue);
                }
                else
                {
                    MonthPayment[monthCreate] = newValue;
                }
            }

            public decimal GetMonthBudgetPayment(int monthCreate)
            {
                if (MonthPayment.Any(o => o.Key == monthCreate))
                {
                    return MonthPayment[monthCreate].Key;
                }
                else
                {
                    return 0;
                }
            }

            public decimal GetMonthOutBudgetPayment(int monthCreate)
            {
                if (MonthPayment.Any(o => o.Key == monthCreate))
                {
                    return MonthPayment[monthCreate].Value;
                }
                else
                {
                    return 0;
                }
            }

            public decimal GetAllMonthBudgetAmount()
            {
                return MonthPayment.Sum(o => o.Value.Key);
            }

            public decimal GetAllMonthOutBudgetAmount()
            {
                return MonthPayment.Sum(o => o.Value.Value);
            }
        }

        private string BudgetMonthToColName(int monthCreate, int row)
        {
            string colMonthCrateBudget;
            switch (monthCreate)
            {
                case 10:
                    colMonthCrateBudget = "G" + row;
                    break;
                case 11:
                    colMonthCrateBudget = "I" + row;
                    break;
                case 12:
                    colMonthCrateBudget = "K" + row;
                    break;

                case 1:
                    colMonthCrateBudget = "M" + row;
                    break;
                case 2:
                    colMonthCrateBudget = "O" + row;
                    break;
                case 3:
                    colMonthCrateBudget = "Q" + row;
                    break;

                case 4:
                    colMonthCrateBudget = "S" + row;
                    break;
                case 5:
                    colMonthCrateBudget = "U" + row;
                    break;
                case 6:
                    colMonthCrateBudget = "W" + row;
                    break;

                case 7:
                    colMonthCrateBudget = "Y" + row;
                    break;
                case 8:
                    colMonthCrateBudget = "AA" + row;
                    break;
                case 9:
                    colMonthCrateBudget = "AC" + row;
                    break;
                default:
                    throw new Exception("None");
            }
            return colMonthCrateBudget;
        }

        private string OutBudgetMonthToColName(int monthCreate, int row)
        {
            string colMonthCrateOutBudget;
            switch (monthCreate)
            {
                case 10:
                    colMonthCrateOutBudget = "H" + row;
                    break;
                case 11:
                    colMonthCrateOutBudget = "J" + row;
                    break;
                case 12:
                    colMonthCrateOutBudget = "L" + row;
                    break;

                case 1:
                    colMonthCrateOutBudget = "N" + row;
                    break;
                case 2:
                    colMonthCrateOutBudget = "P" + row;
                    break;
                case 3:
                    colMonthCrateOutBudget = "R" + row;
                    break;

                case 4:
                    colMonthCrateOutBudget = "T" + row;
                    break;
                case 5:
                    colMonthCrateOutBudget = "V" + row;
                    break;
                case 6:
                    colMonthCrateOutBudget = "X" + row;
                    break;

                case 7:
                    colMonthCrateOutBudget = "Z" + row;
                    break;
                case 8:
                    colMonthCrateOutBudget = "AB" + row;
                    break;
                case 9:
                    colMonthCrateOutBudget = "AD" + row;
                    break;
                default:
                    throw new Exception("None");
            }
            return colMonthCrateOutBudget;
        }

        #endregion
    }
}